﻿using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace APP.HELPER.Data.Helper
{
    public class SqlAccess
    {
        private string _strConn;
        private OleDbConnection _myConn;
        private OleDbTransaction _oleDbTrans;
        public string ConnectString
        {
            get
            {
                return this._strConn;
            }
            set
            {
                this._strConn = value;
            }
        }
        public OleDbConnection Connection
        {
            get
            {
                return this._myConn;
            }
        }
        public OleDbTransaction Transaction
        {
            get
            {
                return this._oleDbTrans;
            }
        }
        ~SqlAccess()
        {
            this._myConn = null;
        }
        public SqlAccess()
        {
            if (ConfigurationSettings.AppSettings["connectionString"] == null)
            {
                throw new ArgumentNullException("Không thể đọc được chuỗi \"ConnectString\" trong tập tin .Config.");
            }
            this._strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + ConfigurationSettings.AppSettings["ConnectionString"].ToString();
            this._myConn = new OleDbConnection();
        }
        public SqlAccess(string sLocation, string sDBName, string sPassword)
        {
            this._strConn = string.Concat(new string[]
			{
				"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=",
				sLocation,
				sDBName,
				";Persist Security Info=False;Jet OLEDB:Database Password=",
				sPassword
			});
            this._myConn = new OleDbConnection();
        }
        public SqlAccess(string sDBName, string sPassword)
        {
            this._strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sDBName + ";Persist Security Info=False;Jet OLEDB:Database Password=" + sPassword;
            this._myConn = new OleDbConnection();
        }
        public SqlAccess(string sDBName)
        {
            this._strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sDBName + ";Persist Security Info=False;Jet OLEDB:Database Password=";
            this._myConn = new OleDbConnection();
        }
        public string Open()
        {
            if (this._myConn == null)
            {
                throw new Exception("Connect is null");
            }
            string result;
            try
            {
                this._myConn.ConnectionString = this._strConn;
                if (this._myConn.State == ConnectionState.Closed)
                {
                    this._myConn.Open();
                }
                result = "OK";
            }
            catch (OleDbException ex)
            {
                string arg_4B_0 = ex.Message;
                throw new Exception(ex.Message);
            }
            return result;
        }
        public string Start()
        {
            string text = this.Open();
            if (text != "OK")
            {
                return text;
            }
            string result;
            try
            {
                this._oleDbTrans = this._myConn.BeginTransaction();
                result = "OK";
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return result;
        }
        public string Commit()
        {
            string result;
            try
            {
                this._oleDbTrans.Commit();
                result = "OK";
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                throw new Exception(message);
            }
            return result;
        }
        public string Rollback()
        {
            string result = "";
            try
            {
                this._oleDbTrans.Rollback();
                return "OK";
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
        public void Close()
        {
            if (this._myConn.State == ConnectionState.Open)
            {
                this._myConn.Close();
                this._myConn.Dispose();
                this._myConn = null;
            }
        }
        public string ExecuteNonQuery(string commandText)
        {
            return this.ExecuteNonQuery(commandText, null, null);
        }
        public string ExecuteNonQuery(string commandText, string[] myPara, object[] myValue)
        {
            string text = "";
            text = this.Open();
            if (text != "OK")
            {
                return text;
            }
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.CommandText = commandText;
                oleDbCommand.Connection = this._myConn;
                if (myPara != null)
                {
                    oleDbCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < myPara.Length; i++)
                    {
                        OleDbParameter value = new OleDbParameter(myPara[i], myValue[i]);
                        oleDbCommand.Parameters.Add(value);
                    }
                }
                oleDbCommand.ExecuteNonQuery();
                text = "OK";
            }
            catch (Exception ex)
            {
                text = ex.Message;
                throw new Exception(text);
            }
            finally
            {
                this.Close();
            }
            return text;
        }
        public string ExecuteNonQuery(OleDbTransaction myTransaction, string commandText, string[] myPara, object[] myValue)
        {
            string text = "";
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.CommandText = commandText;
                oleDbCommand.Connection = myTransaction.Connection;
                oleDbCommand.Transaction = myTransaction;
                if (myPara != null)
                {
                    oleDbCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < myPara.Length; i++)
                    {
                        OleDbParameter value = new OleDbParameter(myPara[i], myValue[i]);
                        oleDbCommand.Parameters.Add(value);
                    }
                }
                oleDbCommand.ExecuteNonQuery();
                text = "OK";
            }
            catch (Exception ex)
            {
                text = ex.Message;
                this.Rollback();
                throw new Exception(text);
            }
            return text;
        }
        public string ExecuteNonQuery(OleDbTransaction myTransaction, string commandText)
        {
            return this.ExecuteNonQuery(myTransaction, commandText, null, null);
        }
        public DataTable ExecuteDataTable(string commandText, string[] myPara, object[] myValue)
        {
            DataTable dataTable = new DataTable();
            string text = this.Open();
            if (text != "OK")
            {
                return dataTable;
            }
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.CommandText = commandText;
                oleDbCommand.Connection = this._myConn;
                if (myPara != null)
                {
                    oleDbCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < myPara.Length; i++)
                    {
                        OleDbParameter value = new OleDbParameter(myPara[i], myValue[i]);
                        oleDbCommand.Parameters.Add(value);
                    }
                }
                OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);
                oleDbDataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                text = ex.Message;
                throw new Exception(text);
            }
            finally
            {
                this.Close();
            }
            return dataTable;
        }
        public DataTable ExecuteDataTable(OleDbTransaction myTransaction, string commandText)
        {
            DataTable dataTable = new DataTable();
            try
            {
                OleDbCommand selectCommand = new OleDbCommand(commandText, myTransaction.Connection, myTransaction);
                OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(selectCommand);
                oleDbDataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dataTable;
        }
        public DataTable ExecuteDataTable(string commandText)
        {
            return this.ExecuteDataTable(commandText, null, null);
        }
        public object ExecuteScalar(string commandText, string[] myPara, object[] myValue)
        {
            object result = null;
            string text = this.Open();
            if (text != "OK")
            {
                return result;
            }
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand();
                oleDbCommand.CommandText = commandText;
                oleDbCommand.Connection = this._myConn;
                if (myPara != null)
                {
                    oleDbCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < myPara.Length; i++)
                    {
                        OleDbParameter value = new OleDbParameter(myPara[i], myValue[i]);
                        oleDbCommand.Parameters.Add(value);
                    }
                }
                result = oleDbCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                result = null;
                text = ex.Message;
                throw new Exception(text);
            }
            finally
            {
                this.Close();
            }
            return result;
        }
        public object ExecuteScalar(string commandText)
        {
            return this.ExecuteScalar(commandText, null, null);
        }
        public OleDbDataReader ExecuteReader(string commandText)
        {
            OleDbDataReader result = null;
            if (this.Open() == "OK")
            {
                result = this.ExecuteReader(this._myConn, commandText);
            }
            return result;
        }
        public OleDbDataReader ExecuteReader(OleDbConnection myConnection, string commandText)
        {
            OleDbDataReader result;
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand(commandText, myConnection);
                result = oleDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return result;
        }
        public OleDbDataReader ExecuteReader(OleDbTransaction myTransaction, string commandText)
        {
            OleDbDataReader result;
            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand(commandText, myTransaction.Connection, myTransaction);
                result = oleDbCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return result;
        }
    }
}
